Create Database [TUNGA_DB]
go
use [TUNGA_DB]
go
CREATE TABLE [Contact]
(
	id int primary key identity,
	phone nvarchar(20),
	number nvarchar(5),
	street nvarchar(50),
	ward nvarchar(50),
	district nvarchar(50),
	city nvarchar(50),
	email nvarchar(50) unique
)
go
CREATE TABLE [Account]
(
	id int PRIMARY KEY identity,
	[username] nvarchar(50) unique,
	password nvarchar(50),
	fullname nvarchar(100),
	contactID int unique references [Contact] (id),
	dob datetime,
	userRole nvarchar(10) default 'user',
	imgPath nvarchar(200)
)
go
CREATE TABLE [Restaurant]
(
	id INT PRIMARY KEY IDENTITY,
	[name] nvarchar(30) unique,
	contactID int unique references [Contact] (id),
	imgPath nvarchar(200)
)
go
CREATE TABLE [RoomType]
(
	id INT PRIMARY KEY IDENTITY,
	[type] nvarchar(20) unique,
	description nvarchar(max),
	surcharge float
)
go
CREATE TABLE [DiningRoom]
(
	id INT PRIMARY KEY IDENTITY,
	typeID INT REFERENCES [RoomType] (id),
	restaurantID INT REFERENCES [Restaurant] (id),
	imgPath nvarchar(200)
)
go
CREATE TABLE [TableType]
(
	id INT PRIMARY KEY IDENTITY,
	[type] nvarchar(20),
	description nvarchar(max)
)
go
CREATE TABLE [RestaurantTable]
(
	id INT PRIMARY KEY IDENTITY,
	typeID INT REFERENCES [TableType] (id),
	roomID INT REFERENCES [DiningRoom] (id),
	imgPath nvarchar(200)
)
go
CREATE TABLE [FoodType]
(
	id INT PRIMARY KEY IDENTITY,
	[category] nvarchar(30)
)
go
CREATE TABLE [Food]
(
	id INT PRIMARY KEY IDENTITY,
	foodName nvarchar(200),
	foodType INT REFERENCES [FoodType] (id),
	isVegetarian bit,
	price float,
	imgPath nvarchar(200)
)
go
CREATE TABLE [Reservation]
(
	id nvarchar(50) PRIMARY KEY,
	[accID] int references [Account] (id),
	reserDate datetime,
	total float,
	isProcessed bit
)
go
CREATE TABLE [ReservationDetails]
(
	[reservationID] nvarchar(50) references [Reservation] (id),
	[tableID] int references [RestaurantTable] (id),
	arrivedDate datetime,
	quantity int
	constraint ResDetail_pk Primary key ([reservationID], [tableID])
)
go
CREATE TABLE [FoodOrder]
(
	id nvarchar(50) PRIMARY KEY,
	[accID] int references [Account] (id),
	orderDate datetime,
	total float,
	isProcessed bit
)
go 
CREATE TABLE [OrderDetails]
(
	[orderID] nvarchar(50) references [FoodOrder] (id),
	[foodID] int references [Food] (id),
	deliveryDate datetime,
	phone nvarchar(20),
	number nvarchar(5),
	street nvarchar(50),
	ward nvarchar(50),
	district nvarchar(50),
	city nvarchar(50),
	quantity int,
	constraint OrderDetail_pk Primary key ([orderID], [foodID])
)
